import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def hello():
    print('subcompany operation')


def getAllStation():
    print('return all station')
    connect.ping(reconnect=True)

    sql = "SELECT station.station_id,station.station_name,station.attr_line_id,line.line_name " \
          "FROM station left join line on station.attr_line_id=line.line_id order by station.station_id"
    print(sql)

    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有站点")
        return 0
    else:
        print("查询成功")
        return temp


def newstation(station_name, attr_line_id):
    print('insert a new station')

    number = getnewnumber()

    sql = "insert into station values( '" + str(number) + "', '" + station_name + "','" + attr_line_id + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(station_id) from station"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def editstation(station_id, station_name, attr_line_id):
    print('edit a station')

    sql = "update station set station_name='" + station_name + "',attr_line_id='" + attr_line_id + "' where station_id=" + station_id
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchStation(word):
    sql = "SELECT station.station_id,station.station_name,station.attr_line_id,line.line_name " \
          "FROM station,line WHERE station_name LIKE '%" + word + "%' and station.attr_line_id=line.line_id"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的站点")
        return 0
    else:
        print("搜索成功")
        return temp


def checkdelete(word):
    result = ""

    # 查看站点是否被线路经过
    sql = "select line_id from line where line_station_id like '%" + str(word) + "%'"
    cursor.execute(sql)
    lines = cursor.fetchall()
    print(lines)

    if len(lines) != 0:
        linename_temp = ''
        for line in lines:
            linename_temp += line[0] + "、"
        linename = linename_temp[0:len(linename_temp) - 1]
        result = "该站点正由" + linename + "经过，无法删除。"

    return result


def deleteStation(word):
    sql = "Delete FROM station WHERE station_id =" + str(word)
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getOptions():
    print('return all line')

    sql = "SELECT distinct line_id,line_name FROM line"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有线路")
        return 0
    else:
        print("查询成功")
        return temp
